package com.zoujt.dao;

import com.zoujt.entitys.User;
import com.zoujt.untils.DateUtil;
import com.zoujt.untils.DbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

/*
 * @auth zoujt
 * @date 2021/2/25
 * @Description
 */
public class UserDao {

    //登录验证
        public  ArrayList<User> checkNameAndPassword(String username, String password){
            ArrayList<User> list = new ArrayList();
            Connection conn = null;
            PreparedStatement prep = null;
            ResultSet rs = null;
            try {
                conn = DbUtil.getConn();
                String sql = "select * from sys_user where username=? and password=?";
                prep = conn.prepareStatement(sql);
                prep.setString(1,username);
                prep.setString(2,password);
                rs = prep.executeQuery();
                while (rs.next()){
                    User user = new User();
                    String name = rs.getString("username");
                    String ps= rs.getString("password");
                    user.setUsername(name);
                    user.setPassword(ps);
                    list.add(user);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    DbUtil.getConn().close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return list;

        }

    //查询所有
    public ArrayList<User> listAll(){
        ArrayList<User> list = new ArrayList();
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            conn = DbUtil.getConn();
            String sql = "select * from sys_user";
            prep = conn.prepareStatement(sql);
            rs = prep.executeQuery();
            while (rs.next()){
                Integer id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                Integer sex = rs.getInt("sex");
                Timestamp createTime = rs.getTimestamp("create_time");
                String date = DateUtil.getDate(createTime);
                User user = new User();
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setSex(sex);
                user.setCreateTime(date);
                list.add(user);

            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.getConn().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }

    //添加用户
    public void userAdd(User user) {
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;

        try {
            conn = DbUtil.getConn();
            String sql = "insert into sys_user(id,username,password,dept_id,sex,create_time) values (null,?,?,?,?,?)";
            prep = conn.prepareStatement(sql);
            prep.setString(1, user.getUsername());
            prep.setString(2, user.getPassword());
            prep.setInt(3, user.getDeptId());
            prep.setInt(4, user.getSex());
            prep.setString(5, user.getCreateTime());
            prep.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.getConn().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
    //删除用户
    public void userDelete(Integer id){
        Connection conn = null;
        PreparedStatement prep = null;

        try {
            conn = DbUtil.getConn();
            String sql = "delete from sys_user where id = ?";
            prep= conn.prepareStatement(sql);
            prep.setInt(1,id);
            prep.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(prep,conn);
        }

    }

    //修改
    public User updateById(Integer id){
            User user = new User();
            Connection conn = null;
            PreparedStatement prep = null;
            ResultSet rs = null;

        try {
            conn = DbUtil.getConn();
            String sql = "select * from sys_user where id = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,id);
            rs=  prep.executeQuery();
            while (rs.next()){
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setDeptId(rs.getInt("dept_id"));
                user.setSex(rs.getInt("sex"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.getConn().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }



    public void userUpdate(User user) {
            Connection conn = null;
            PreparedStatement prep=null;
        try {
            conn = DbUtil.getConn();
            String sql = "update sys_user set username = ?,password = ?,dept_id=?,sex=? where id =?";
            prep = conn.prepareStatement(sql);
            prep.setString(1,user.getUsername());
            prep.setString(2,user.getPassword());
            prep.setInt(3,user.getDeptId());
            prep.setInt(4,user.getSex());
            prep.setInt(5,user.getId());
            prep.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.getConn().close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

}
